# -*- coding: utf-8 -*-
'''
| @author: stone.li
| Created on: 2022/12/1 9:01
| desc: 技术链页面SQL查询
'''

import logging, datetime, time, requests, traceback, functools
import base.globalvars as glo
from proj_spec.IDS.comparison_industries import chain_id_transformation
from util.db_util import execute_query,get_query_result


def get_node_name(node_code):
    """查询节点name
    :param node_code：节点code
    :return: 节点name
    """
    sql_sam_code = "select name from datascope.dict_product_rs where code = '%s'" % node_code
    try:
        res = execute_query(sql_sam_code)
        return res[0][0]
    except Exception as e:
        logging.error("get_node_name-%s:%s产业节点不存在" % (e, node_code))


def get_node_patent(region_code, tree_id, nr):
    """查询地区节点专利数量（统计）
    :param region_code：地区CODE
    :param tree_id：产业链ID
    :param nr：3全国/1地区
    :return: 节点专利数量
    """
    chain_his_id = chain_id_transformation(tree_id)
    sql_patent = "SELECT sam_code,patent_count FROM industry_cockpit.t_sam_patent_top10 WHERE chain_his_id = %s AND region_code = '%s' AND type = %s" % (chain_his_id, region_code, nr)
    try:
        res = execute_query(sql_patent, dbname="DB_cockpit")
    except Exception as e:
        logging.error("get_node_patent--SQL查询失败：%s" % e)
    # 10大节点的专利数量
    node_patent_list = []
    for i in res:
        k = list(i)
        # dict_product_rs表根据node_code查不到node_name,开发排查中
        k[0] = get_node_name(k[0])
        node_patent_list.append(k)
    return node_patent_list


def get_node_patent_growth(region_code, tree_id, nr):
    """查询地区节点专利增速
    :param region_code：地区CODE
    :param tree_id：产业链ID
    :param nr：3-全国/1-地区
    :return: 节点专利增速
    """
    datetime_year = datetime.date.today().year - 1
    chain_his_id = chain_id_transformation(tree_id)
    # 1、先查出节点所有patent_id
    sql_patent = "SELECT sam_code,patent_ids FROM industry_cockpit.t_sam_patent_top10 WHERE chain_his_id = %s AND region_code = '%s' AND type = %s" % (chain_his_id, region_code, nr)
    try:
        res = execute_query(sql_patent, dbname="DB_cockpit")
    except Exception as e:
        logging.error("%sSQL执行失败：%s" % (sql_patent, e))
    # 2、把所有patent_id代入t_company_sam_patent_info_calc查询当年和去年的专利数量，计算增幅
    patent_rate_list = []
    for patent_ids in res:
        # 当期值
        patent_id_count_01 = get_node_patent_count(patent_ids, datetime_year)
        # 上期值
        patent_id_count_02 = get_node_patent_count(patent_ids, datetime_year-1)
        # 上期值与当期值可能都为0，增速为0
        if patent_id_count_01 == 0 and patent_id_count_02 == 0:
            Result = 0.0
        # 上期值为0，当期值不为0时，增速为100%
        elif patent_id_count_02 == 0 and patent_id_count_01 != 0:
            Result = 1.0
        # 正常情况增速
        else:
            Result = round((patent_id_count_01 - patent_id_count_02)/patent_id_count_02, 2)
        patent_rate_list.append([patent_ids[0], Result])
    return patent_rate_list


def get_node_patent_count(patent_ids, data_year):
    """查询地区节点专利数量（实际）
    :param patent_ids：专利ID
    :param data_year：时间
    :return: 专利数量
    """
    first_day = "%s-01-01" % data_year
    last_day = "%s-12-31" % data_year
    sql = '''SELECT count(*) FROM industry_cockpit.t_company_sam_patent_info_calc WHERE patent_id IN {} and pubdate BETWEEN '%s' AND '%s' '''.format(tuple(patent_ids[1].split(";"))) % (first_day, last_day)
    try:
        res = execute_query(sql, dbname="DB_cockpit")
        return res[0][0]
    except Exception as e:
        logging.error("get_node_patent_count--SQL查询失败：%s" % e)